package mac5855.app.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import mac5855.app.models.Categoria;
import mac5855.app.models.Denuncia;
import mac5855.app.models.Pergunta;
import mac5855.app.models.Resposta;
import mac5855.app.models.Usuario;

public class JdbcDenunciaDAO implements DenunciaDAO {

	@Override
	public void create(Denuncia newInstance) {
		Connection conn = null;
		conn = JdbcDAOFactory.createConnection();
		try {
			String column = (newInstance.getPergunta() != null) ? "pergunta_id" : "resposta_id";
			int id = (newInstance.getPergunta() != null) ? newInstance.getPergunta().getId() : newInstance.getResposta().getId();
			PreparedStatement stmt = conn.prepareStatement("insert into denuncias (motivo, data, usuario_id, "+column+") values (?,?,?,?)");

			stmt.setString(1, newInstance.getMotivo());
			stmt.setDate(2, new java.sql.Date(newInstance.getData().getTime()));
			stmt.setString(3, newInstance.getUsuario().getEmail());
			stmt.setInt(4, id);

			stmt.execute();

			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("Gravado!");
		JdbcDAOFactory.releaseConnection(conn);

	}

	@Override
	public void delete(Integer id) {
		// TODO Auto-generated method stub

	}

	@Override
	public Denuncia find(Denuncia object) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Denuncia[] findAll() {
		Connection conn = JdbcDAOFactory.createConnection();
		PerguntaDAO perguntaDAO =  DAOFactory.getDAOFactory(1).getPerguntaDAO();
		RespostaDAO respostaDAO =  DAOFactory.getDAOFactory(1).getRespostaDAO();
		
		
		List<Denuncia> denuncias = new ArrayList<Denuncia>();
		try {
			PreparedStatement stmt = conn.prepareStatement("select * from denuncias");
			ResultSet rs = stmt.executeQuery();


			while (rs.next()) {
				// criando o objeto Contato motivo, data, usuario_id  "pergunta_id" : "resposta_id"
				Denuncia denuncia = new Denuncia();
				denuncia.setId(rs.getInt("id"));
				denuncia.setMotivo(rs.getString("motivo"));
				denuncia.setData(rs.getDate("data"));

				Integer pergunta_id = rs.getInt("pergunta_id");
				if(pergunta_id != null)
				{
					Pergunta pergunta = perguntaDAO.findById(pergunta_id);
					denuncia.setPergunta(pergunta);
				}
				
				Integer resposta_id = rs.getInt("resposta_id");
				if(resposta_id != null)
				{
					Resposta resposta = respostaDAO.findById(resposta_id);
					denuncia.setResposta(resposta);
				}

				// adicionando o objeto à lista
				denuncias.add(denuncia);
			}

			rs.close();
			stmt.close();
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		JdbcDAOFactory.releaseConnection(conn);

		return denuncias.toArray(new Denuncia[denuncias.size()]);

	}

	@Override
	public void update(Denuncia transientObject) {
		// TODO Auto-generated method stub

	}

	@Override
	public Denuncia findById(Integer id) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int findNumDenunciasPergunta(Integer pergunta_id) {
		Connection conn = JdbcDAOFactory.createConnection();
		int num = 0;
		try {
			PreparedStatement stmt = conn.prepareStatement("select count(*) from denuncias where pergunta_id = ?");
			stmt.setInt(1, pergunta_id);
			ResultSet rs = stmt.executeQuery();

			
			if (rs.next()) {
				num = rs.getInt(1);
			
			}
			rs.close();
			stmt.close();
			
			
			
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		JdbcDAOFactory.releaseConnection(conn);

		return num;
	}
	
	public int findNumDenunciasResposta(Integer resposta_id) {
		Connection conn = JdbcDAOFactory.createConnection();
		int num = 0;
		try {
			PreparedStatement stmt = conn.prepareStatement("select count(*) from denuncias where resposta_id = ?");
			stmt.setInt(1, resposta_id);
			ResultSet rs = stmt.executeQuery();

			
			if (rs.next()) {
				num = rs.getInt(1);
			
			}
			rs.close();
			stmt.close();
			
			
			
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		JdbcDAOFactory.releaseConnection(conn);

		return num;
	}

}
